package com.caiyi.financial.nirvana.sms.mapper;

import com.caiyi.financial.nirvana.sms.base.mapper.BaseMapper;
import com.caiyi.financial.nirvana.sms.entity.SmsMsg;
import com.caiyi.financial.nirvana.sms.querybean.*;
import com.caiyi.financial.nirvana.sms.queue.SendQueueEntity;
import com.caiyi.financial.nirvana.sms.vo.SmsAccountVo;
import com.caiyi.financial.nirvana.sms.vo.SmsPriceVo;
import com.caiyi.financial.nirvana.sms.vo.SmsRateVo;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;
import java.util.Map;

@Mapper
public interface SmsMsgMapper extends BaseMapper<SmsMsg> {

    //====================================统计开始===============================//
    @Select("select ifnull(sum(msg_count),0) as msgCount from sms_day_account m where m.gateway_id  = #{gateWayId} and day >= date_format(DATE_ADD(now(),interval -day(now())+1 day),'%Y-%m-%d') ")
    int getCountByGetWayId(@Param("gateWayId") Integer gateWayId);

    @SelectProvider(type = MsgSqlProvider.class, method = "selectMsgByDayByAccount")
    List<SmsAccountVo> selectMsgByDayByAccount(MsgDayQueryBean bean);

    @SelectProvider(type = MsgSqlProvider.class, method = "selectMsgByMonthByAccount")
    List<SmsAccountVo> selectMsgByMonthByAccount(MsgMonthQueryBean bean);

    @SelectProvider(type = MsgSqlProvider.class, method = "selectMsgByDayByGateway")
    List<SmsAccountVo> selectMsgByDayByGateway(GatewayDayQueryBean bean);

    @SelectProvider(type = MsgSqlProvider.class, method = "selectMsgByMonthByGateway")
    List<SmsAccountVo> selectMsgByMonthByGateway(GatewayMonthQueryBean bean);

    @SelectProvider(type = MsgSqlProvider.class, method = "selectMsgByDayBySystem")
    List<SmsAccountVo> selectMsgByDayBySystem(MsgDayQueryBean bean);

    @SelectProvider(type = MsgSqlProvider.class, method = "selectMsgByMonthBySystem")
    List<SmsAccountVo> selectMsgByMonthBySystem(MsgMonthQueryBean bean);

    @Select("select date_format(date_add(now(),INTERVAL -4 DAY),'%Y-%m-%d') as day,m.gateway_id as gatewayId,sum(num) as msgCount,ifnull(sum(case when m.status=3 then num else 0 end),0) as successCount,ifnull(sum(case when m.status=1 or m.status=2 then num else 0 end),0) as unknownCount,ifnull(sum(case when m.status=4 or m.status=5 then num else 0 end),0) as failureCount from sms_msg m where send_time >= date_format(date_add(now(),INTERVAL -4 DAY),'%Y-%m-%d') AND send_time < date_format( date_add( now(), INTERVAL -3 DAY ), '%Y-%m-%d' ) GROUP BY day,gateway_id")
    List<Map> searchThreeDaysAgoSmsDayGateway();

    @Update("update sms_day_gateway t2 set t2.msg_count=#{msgCount},t2.success_count=#{successCount},t2.unknown_count=#{unknownCount},t2.failure_count=#{failureCount} where t2.day = #{day} and t2.gateway_id = #{gatewayId}")
    int threeDaysAgoSmsDayGateway(Map<String, String> params);

    @Select("select date_format(date_add(now(),INTERVAL -4 DAY), '%Y-%m-%d') as day,account,ifnull(gateway_id,0) as gatewayId,sum(num) as msgCount ,ifnull(sum(case when status=3 then num else 0 end),0) as successCount,ifnull(sum(case when status=1 or status=2 then num else 0 end),0) as unknownCount,ifnull(sum(case when status=4 or status=5 then num else 0 end),0) as failureCount FROM sms_msg where send_time >= date_format(date_add(now(),INTERVAL -4 DAY),'%Y-%m-%d') AND send_time < date_format( date_add( now(), INTERVAL -3 DAY ), '%Y-%m-%d' ) GROUP BY day,account,gateway_id ")
    List<Map> searchThreeDaysAgoSmsDayAccount();

    @Update("update sms_day_account t2 set t2.msg_count=#{msgCount},t2.success_count=#{successCount},t2.unknown_count=#{unknownCount},t2.failure_count=#{failureCount} where t2.day = #{day} and t2.account = #{account} and t2.gateway_id = #{gatewayId}")
    int threeDaysAgoSmsDayAccount(Map<String, String> params);

    @Insert("INSERT INTO sms_day_account ( DAY, account, msg_count, success_count, unknown_count, failure_count,gateway_id,price ) VALUES (#{day},#{account},#{msgCount},#{successCount},#{unknownCount},#{failureCount},#{gatewayId},#{price})")
    int insertSmsDayAccountUser(Map<String, String> params);

    //先查询在更新
    @Select("SELECT date_format( date_add( now( ), INTERVAL - 1 MINUTE ), '%Y-%m-%d' ) AS day,gateway_id as gatewayId,ifnull((select price from sms_gateway where id = gateway_id),0) as price,account,sum(num) AS msgCount,ifnull( sum( CASE WHEN m.STATUS = 3 THEN num ELSE 0 END ), 0 ) AS successCount,ifnull( sum( CASE WHEN m.STATUS = 1 OR m.STATUS = 2 THEN num ELSE 0 END ), 0 ) AS unknownCount,ifnull( sum( CASE WHEN m.STATUS = 4 OR m.STATUS = 5 THEN num ELSE 0 END ), 0 ) AS failureCount FROM sms_msg m WHERE send_time >= date_format( date_add( now( ), INTERVAL - 1 MINUTE ), '%Y-%m-%d' )  and send_time <= CONCAT(date_format(date_add( now( ), INTERVAL - 1 MINUTE ), '%Y-%m-%d'),' 23:59:59') GROUP BY day,account,gateway_id ")
    List<SmsAccountVo> searchSmsDayAccount();

    @Update("update sms_day_account set msg_count=#{msgCount},success_count=#{successCount},unknown_count=#{unknownCount},failure_count=#{failureCount},price=#{price} where day = #{day} and account = #{account} and gateway_id = #{gatewayId}")
    int updateSmsDayAccount(Map<String, String> params);

    @Select("SELECT gat.gateway_type, msg.gateway_id,gat.name,count(msg.id) AS totalCount,sum( CASE WHEN msg.status = 3 THEN 1 ELSE 0 END ) AS successCount FROM sms_msg msg, sms_gateway gat WHERE msg.gateway_id = gat.id and msg.send_time > date_add( now( ), INTERVAL - 2 HOUR ) AND msg.send_time < date_add( now( ), INTERVAL - 1 HOUR ) group by msg.gateway_id")
    List<SmsRateVo> selectMsgSuccessRate();

    @Select("SELECT msg.gateway_id, gat.name, gat.gateway_type, count(msg.id) AS totalCount, sum( CASE WHEN msg.status = 1 THEN 1 WHEN msg.status = 4 THEN 1 WHEN msg.status = 5 THEN 1 ELSE 0 END ) AS failedCount FROM sms_msg msg, sms_gateway gat WHERE msg.gateway_id = gat.id and msg.send_time > date_add( now( ), INTERVAL - 10 MINUTE ) group by msg.gateway_id")
    List<SmsRateVo> selectMsgFailedRate();

    @Select("SELECT count(1) FROM sms_msg WHERE send_time > date_add( now( ), INTERVAL - 30 MINUTE ) and create_time > date_add( now( ), INTERVAL - 10 MINUTE )")
    long countSmsMsg();

    @Select("select ifnull(g.name,'未知') as name,ifnull(g.charging_type,1) as chargingType,(CASE g.charging_type WHEN 2 THEN success_count ELSE msg_count END ) AS msgCount,t.price,(CASE g.charging_type WHEN 2 THEN round((t.success_count*t.price),2) ELSE round((t.msg_count*t.price),2) END) as totalPrice from sms_day_account t left join sms_gateway g on t.gateway_id = g.id where t.day = #{date} and t.account = #{account} ORDER BY msgCount desc")
    List<SmsPriceVo> getDayBillDetail(@Param("date") String date, @Param("account") String account);

    @Select("SELECT t.gateway_id as gatewayId,ifnull( g.NAME, '未知' ) AS name,ifnull( g.charging_type, 1 ) AS chargingType,( CASE g.charging_type WHEN 2 THEN sum(success_count) ELSE sum(msg_count) END ) AS msgCount,t.price,(CASE g.charging_type WHEN 2 THEN round( ( sum(t.success_count) * t.price ), 2 ) ELSE round( ( sum(t.msg_count * t.price) ), 2 ) END ) AS totalPrice FROM sms_day_account t LEFT JOIN sms_gateway g ON t.gateway_id = g.id where date_format(t.day, '%Y-%m')  = #{date} and t.account = #{account} GROUP BY t.gateway_id,t.price ORDER BY msgCount desc")
    List<SmsPriceVo> getMonthBillDetail(@Param("date") String date, @Param("account") String account);


    @Select("select t.gateway_id as gatewayId,g.price,g.name FROM sms_day_account t left JOIN sms_gateway g ON t.gateway_id = g.id  where  g.name is not null and t.account in (${accounts})  and date_format( t.day, '%Y-%m') = #{month} GROUP BY gateway_id ORDER BY g.id asc")
    List<SmsPriceVo> getMonthGateway(@Param("month") String month, @Param("accounts") String accounts);

    @Select("select t.account,(select company_name from sms_user where account = t.account) as name,t.gateway_id as gatewayId,( CASE g.charging_type WHEN 2 THEN sum(success_count) ELSE sum(msg_count) END ) AS msgCount  FROM sms_day_account t LEFT JOIN sms_gateway g ON t.gateway_id = g.id where gateway_id > 0 and date_format(t.day, '%Y-%m') = #{month}  GROUP BY t.account,t.gateway_id ")
    List<SmsPriceVo> getMsgMonthExcel(String month);
    //====================================统计结束===============================//

    //====================================TEST===============================//
    @Select("select date_format(date_add(now(),INTERVAL - #{start} DAY), '%Y-%m-%d') as day,account,ifnull((select price from sms_gateway where id = gateway_id),0) as price,ifnull(gateway_id,0)  as gatewayId,sum(num) as msgCount ,ifnull(sum(case when status=3 then num else 0 end),0) as successCount,ifnull(sum(case when status=1 or status=2 then num else 0 end),0) as unknownCount,ifnull(sum(case when status=4 or status=5 then num else 0 end),0) as failureCount FROM sms_msg where send_time >= date_format(date_add(now(),INTERVAL - #{start} DAY),'%Y-%m-%d') AND send_time < date_format( date_add( now(), INTERVAL - #{end} DAY ), '%Y-%m-%d' ) GROUP BY day,account,gateway_id ")
    List<Map> searchThreeDayDetail(@Param("start") int start, @Param("end") int end);

    @Update("update sms_day_account t2 set t2.msg_count=#{msgCount},t2.success_count=#{successCount},t2.unknown_count=#{unknownCount},t2.failure_count=#{failureCount},t2.price = #{price} where t2.day = #{day} and t2.account = #{account} and t2.gateway_id = #{gatewayId}")
    int updateThreeDayDetail(Map<String, String> params);
    //====================================TEST END===============================//

    @Select("SELECT s.phone  from sms_msg s where s.send_time >= date_add(now(),INTERVAL -1 HOUR) and s.province is null")
    List<String> getSmsByProvince();

    @Update("update  sms_msg s set s.province = #{province} ,s.city = #{city} where s.send_time >= date_add(now(),INTERVAL -1 HOUR) and s.phone=#{phone}")
    int updateProvinceAndCityByPhone(@Param("province") String province, @Param("city") String city, @Param("phone") String phone);

    @SelectProvider(type = MsgSqlProvider.class, method = "selectMsgListByParams")
    List<SmsMsg> selectMsgListByParams(QueryMsgBean bean);

    @Select("select s.message_id,s.batch_id,s.receive_time,m.notify_url,s.account,s.phone,m.uid,s.result from sms_msg s ,sms_batch_msg m where s.send_time <= date_add(now(), interval -3 day) and s.send_time > date_add(now(), interval -4 day) and (s.status = 1 or s.status = 2) and s.batch_id = m.batch_id")
    List<SendQueueEntity> selectMsgByStatus();

    class MsgSqlProvider {
        public String selectMsgListByParams(QueryMsgBean bean) {
            return new SQL() {
                {
                    SELECT("sms_msg.id,sms_msg.batch_id as batchId,sms_msg.message_id as messageId,sms_msg.account,sms_msg.province,sms_msg.city,sms_msg.phone,sms_msg.operator*1 as operator,sms_msg.content,sms_msg.gateway_id as gatewayId,sms_msg.ip,sms_msg.send_time as sendTime,case when sms_msg.status=1 or sms_msg.status=2 then null else  sms_msg.receive_time end as receiveTime,sms_msg.status*1 as status,sms_msg.access_number as accessNumber,sms_msg.result,sms_msg.num,sms_msg.create_time as createTime,sms_msg.update_time as updateTime,case when sms_msg.status=1 or sms_msg.status=2 then null else TIMESTAMPDIFF(SECOND,sms_msg.send_time,sms_msg.receive_time) end as countTime");
                    FROM("sms_msg");
                    if (StringUtils.isNotBlank(bean.getBatchId())) {
                        WHERE("sms_msg.batch_id=#{batchId}");
                    }
                    if (StringUtils.isNotBlank(bean.getMessageId())) {
                        WHERE("sms_msg.message_id=#{messageId}");
                    }
                    if (StringUtils.isNotBlank(bean.getAccount())) {
                        WHERE("sms_msg.account=#{account}");
                    }
                    if (StringUtils.isNotBlank(bean.getContent())) {
                        WHERE("content LIKE concat('%',#{content},'%')");
                    }
                    if (StringUtils.isNotBlank(bean.getResult())) {
                        WHERE("result LIKE concat('%',#{result},'%')");
                    }
                    if (bean.getNum() != null) {
                        if (bean.getNum() == 1) {
                            WHERE("sms_msg.num=#{num}");
                        } else {
                            WHERE("sms_msg.num > 1");
                        }
                    }
                    if (StringUtils.isNotBlank(bean.getPhone())) {
                        WHERE("sms_msg.phone=#{phone}");
                    }
                    if (StringUtils.isNotBlank(bean.getGatewayId())) {
                        WHERE("sms_msg.gateway_id = #{gatewayId}");
                    }
                    if (bean.getOperator() != null) {
                        WHERE("sms_msg.operator=#{operator}");
                    }
                    if (StringUtils.isNotBlank(bean.getAccessNumber())) {
                        WHERE("sms_msg.access_number=#{accessNumber}");
                    }
                    if (bean.getStatus() != null) {
                        WHERE("sms_msg.status=#{status}");
                    }
                    if (StringUtils.isNotBlank(bean.getCity())) {
                        WHERE("sms_msg.city=#{city}");
                    }
                    if (StringUtils.isNotBlank(bean.getProvince())) {
                        WHERE("sms_msg.province=#{province}");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("sms_msg.send_time >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("sms_msg.send_time < date_add(#{endTime}, interval 1 day) ");
                    }
                    ORDER_BY("sms_msg.send_time desc");


                }
            }.toString();
        }

        public String selectMsgByDayByAccount(MsgDayQueryBean bean) {
            return new SQL() {
                {
                    SELECT(" date_format( DAY, '%Y-%m-%d' ) AS day,account,(SELECT company_name FROM sms_user t2 WHERE t1.account = t2.account ) AS companyName,sum(msg_count) AS msgCount,sum(success_count) AS successCount,sum(unknown_count) AS unknownCount,sum(failure_count) AS failureCount,CONCAT( TRUNCATE ( sum(success_count) / sum(msg_count) * 100, 2 ), '%' ) AS successRate");
                    FROM("sms_day_account t1");
                    if (!StringUtils.isEmpty(bean.getAccount())) {
                        WHERE("account = #{account}");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("day >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("day <= #{endTime}");
                    }
                    GROUP_BY("day,account");
                    ORDER_BY("day,msgCount desc");
                }
            }.toString();

        }

        public String selectMsgByMonthByAccount(MsgMonthQueryBean bean) {
            return new SQL() {
                {
                    SELECT("date_format(day, '%Y-%m') as month,account,(select company_name from sms_user t2 where t1.account = t2.account) as companyName,sum(msg_count) as msgCount,sum(success_count) as successCount,sum(unknown_count) as unknownCount,sum(failure_count) as failureCount,CONCAT(TRUNCATE(sum(success_count)/sum(msg_count) * 100, 2),'%') as successRate ");
                    FROM("sms_day_account t1");
                    if (!StringUtils.isEmpty(bean.getAccount())) {
                        WHERE("account = #{account}");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("day >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("day < date_add(#{endTime}, interval 1 month)");
                    }
                    GROUP_BY("month,account");
                    ORDER_BY("month,msgCount desc");
                }
            }.toString();

        }

        public String selectMsgByDayByGateway(GatewayDayQueryBean bean) {
            return new SQL() {
                {
                    SELECT("date_format(day, '%Y-%m-%d') as day,g.name as gatewayName,sum(msg_count) as msgCount,sum(success_count) as successCount,sum(unknown_count) as unknownCount,sum(failure_count) as failureCount,CONCAT(TRUNCATE(sum(success_count)/sum(msg_count) * 100, 2),'%') as successRate");
                    FROM("sms_day_account m");
                    LEFT_OUTER_JOIN("sms_gateway g ON m.gateway_id = g.id");
                    if (bean.getGatewayId() != null) {
                        WHERE("m.gateway_id=#{gatewayId}");
                    }
                    if (!StringUtils.isEmpty(bean.getName())) {
                        WHERE("name LIKE concat('%',#{name},'%')");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("m.day >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("m.day <= #{endTime}");
                    }
                    GROUP_BY("m.gateway_id,m.day");
                    ORDER_BY("day,msgCount desc");
                }
            }.toString();

        }

        public String selectMsgByMonthByGateway(GatewayMonthQueryBean bean) {
            return new SQL() {
                {
                    SELECT("date_format(m.day,'%Y-%m') as month,g.name as gatewayName,sum(msg_count) as msgCount,sum(success_count) as successCount,sum(unknown_count) as unknownCount,sum(failure_count) as failureCount,CONCAT(TRUNCATE(sum(success_count)/sum(msg_count) * 100, 2),'%') as successRate");
                    FROM("sms_day_account m");
                    LEFT_OUTER_JOIN("sms_gateway g ON m.gateway_id = g.id");
                    if (bean.getGatewayId() != null) {
                        WHERE("m.gateway_id=#{gatewayId}");
                    }
                    if (!StringUtils.isEmpty(bean.getName())) {
                        WHERE("name LIKE concat('%',#{name},'%')");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("m.day >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("m.day < date_add(#{endTime}, interval 1 month)");
                    }
                    GROUP_BY("month,gateway_id");
                    ORDER_BY("month,msgCount desc");
                }
            }.toString();

        }

        public String selectMsgByDayBySystem(MsgDayQueryBean bean) {
            return new SQL() {
                {
                    SELECT("date_format(day, '%Y-%m-%d') as day,sum(msg_count) as msgCount,sum(case when t3.type=1 then msg_count else 0 end) as industryMsgCount ,sum(case when t3.type=2 then msg_count else 0 end) as marketingMsgCount,sum(success_count) as successCount,sum(unknown_count) as unknownCount,sum(failure_count) as failureCount,CONCAT(TRUNCATE(sum(success_count)/sum(msg_count) * 100, 2),'%') as successRate");
                    FROM("(select t2.*,t1.type from sms_user t1,sms_day_account t2 where t1.account = t2.account) t3");
                    if (!StringUtils.isEmpty(bean.getAccount())) {
                        WHERE("account = #{account}");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("day >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("day <= #{endTime}");
                    }
                    GROUP_BY("day");
                    ORDER_BY("day desc");
                }
            }.toString();

        }

        public String selectMsgByMonthBySystem(MsgMonthQueryBean bean) {
            return new SQL() {
                {
                    SELECT("date_format(day, '%Y-%m') as month,sum(msg_count) as msgCount,sum(case when t3.type=1 then msg_count else 0 end) as industryMsgCount ,sum(case when t3.type=2 then msg_count else 0 end) as marketingMsgCount,sum(success_count) as successCount,sum(unknown_count) as unknownCount,sum(failure_count) as failureCount,CONCAT(TRUNCATE(sum(success_count)/sum(msg_count) * 100, 2),'%') as successRate ");
                    FROM("(select t2.*,t1.type from sms_user t1,sms_day_account t2 where t1.account = t2.account) t3");
                    if (!StringUtils.isEmpty(bean.getAccount())) {
                        WHERE("account = #{account}");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("day >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("day < date_add(#{endTime}, interval 1 month)");
                    }
                    GROUP_BY("month");
                    ORDER_BY("month desc");
                }
            }.toString();

        }

    }

}
